1 'Created on August 16, 2010
2 'Tan, Angelito S.
3
4 'Date update dec 12, 2010
5 Module ModActiveForm
6 Public globalID ' PASS AND USED CURRENT ID, THE MAIN PURPOSE IS TO STORE THE ID
7 Public globalID2
8 Public globalFRM ' PASS AND USED CURRENT FORM NAME, MAIN PURPOSE IS TO GET THE CURRENT OPEN FORMS
9
10 Public Sub FormAdd(ByVal frmStr As String)
11 'MsgBox(frmStr)
12 Select Case UCase(frmStr)
13 Case UCase("FrmSuppliersList")
14 FormShow(FrmAddSupplier, False, 0, 0) ' Add and zero ID !
15
16 Case UCase("frmcatlist")
17 With FrmCatList
18 If .RBCat.Checked Then
19 FormShow(FrmCatADD, False, 0, 0)
20 ElseIf .rbcatitemlist.Checked Then
21 If .lstCategory.Items.Count = 0 Then Exit Sub
22 FormShow(FrmCatITEMADD, False, .lstCategory.FocusedItem.Text, 0)
23 ElseIf .RBALL.Checked Then
24 FormShow(FrmCATITEMADD_2, False, 0, 0)
25 ElseIf .RBGroup.Checked Then
26 FormShow(FrmCAT_GROUP_ADD, False, 0, 0)
27 End If
28 End With
29
30 Case UCase("frmcatitemlist") ' show to add new items
31 FormShow(FrmCatITEMADD, False, FrmCatITEMList.lstCategory.FocusedItem.Text, 0)
32
33 Case UCase("frmSTOCKMONITORINGBALANCES") 'show add stock
34 FormShow(FrmSTOCKADD, False, 0, 0)
35
36 Case UCase("FrmPURCHASEORDER")
37 FormShow(FrmPURCHASEORDERADD, False, 0, 0)
38
39 Case UCase("FrmSupplierProducts")
40 'MsgBox(" Add Supplier Products")
41
42 Case UCase("FrmUNIT_MEASURE_ADD")
43 FormShow(FrmUNIT_MEASURE_ADD, False, 0, 0)
44
45 Case UCase("FrmDEFFECTIVE_RETURN_STOCKS") ' show deffective stocks
46 With FrmDEFFECTIVE_RETURN_STOCKS
47 If .rbDeffect.Checked Then
48 FormShow(FrmDEFFECTIVE_STOCKS_ADD, False, 0, 0)
49 FrmDEFFECTIVE_STOCKS_ADD.txtpo2.Text = 0
50 ElseIf .rbReturn.Checked Then
51 'FrmDEFFECTIVE_RETURN_ADD.txtpo2.Text = 0
52 FormShow(FrmDEFFECTIVE_RETURN_ADD, False, 0, 0)
53 End If
54 End With
55 Case UCase("frmorder_form") 'show order form
56 FormShow(FrmORDER_FORM_DATA, False, 0, 0)
57
58 Case UCase("frmphysicalcount")
59 FormShow(FrmPHYSICALCOUNT_ADD, False, 0, 0)
60 End Select
61 End Sub
62
63 Public Sub FormEdit(ByVal frmStr As String)
64 Select Case UCase(frmStr)
65 Case UCase("FrmSuppliersList")
66 With FrmSuppliersList
67 If .lstsupplier.Items.Count = 0 Then Exit Sub
68 .lstsupplier.Focus()
69 .lstsupplier.Select()
70 FormShow(FrmAddSupplier, True, .lstsupplier.FocusedItem.Text, 0) ' edit and pass the ID !
71 End With
72
73 Case UCase("frmcatlist")
74 With FrmCatList
75 'If .lstCat.Items.Count = 0 Then Exit Sub ' verify if theres a record if zero record found do nothing
76 globalFRM = "frmcatlist"
77 .lstCat.Focus()
78 If .RBCat.Checked Then
79 FormShow(FrmCatADD, True, .lstCat.FocusedItem.Text, 0)
80 ElseIf .rbcatitemlist.Checked Then
81 globalFRM = "frmcatitemlist2"
82
83 .lstItems.Focus()
84 If .lstItems.Items.Count <= 0 Then Exit Sub
85 FormShow(FrmCatITEMADD, True, .lstCategory.FocusedItem.Text, .lstItems.FocusedItem.Text)
86
87 ElseIf .RBALL.Checked Then
88 FormShow(FrmCatITEMADD, True, .lstCat.FocusedItem.SubItems(1).Text, .lstCat.FocusedItem.Text)
89
90 ElseIf .RBGroup.Checked Then
91 FormShow(FrmCAT_GROUP_ADD, True, .lstCat.FocusedItem.Text, 0)
92 End If
93 End With
94
95 Case UCase("frmcatitemlist") ' show to edit new items
96 If FrmCatITEMList.lstItems.Items.Count = 0 Then Exit Sub ' verify if theres a record if zero record found do nothing
97 globalFRM = "frmcatitemlist"
98 FrmCatITEMList.lstItems.Focus()
99 FormShow(FrmCatITEMADD, True, FrmCatITEMList.lstCategory.FocusedItem.Text, FrmCatITEMList.lstItems.FocusedItem.Text)
100
101 'Case UCase("frmSTOCKMONITORINGBALANCES") 'show add stock
102 ' If frmSTOCKMONITORINGBALANCES.listStocks.Items.Count = 0 Then Exit Sub
103 ' FormShow(FrmSTOCKADD, True, frmSTOCKMONITORINGBALANCES.listStocks.FocusedItem.Text, 0)
104
105 Case UCase("FrmPURCHASEORDER")
106 If FrmPURCHASEORDER.listorder.Items.Count = 0 Then Exit Sub
107 FrmPURCHASEORDER.listorder.Focus()
108 FormShow(FrmPURCHASEORDERADD, True, FrmPURCHASEORDER.listorder.FocusedItem.Text, 0)
109
110 Case UCase("FrmSupplierProducts")
111 'MsgBox(" Add Supplier Products")
112 Case UCase("frmUnit_Measure")
113 If FrmUNIT_MEASURE.lstunit.Items.Count = 0 Then Exit Sub
114 FormShow(FrmUNIT_MEASURE_ADD, True, FrmUNIT_MEASURE.lstunit.FocusedItem.Text, 0)
115 Case UCase("FrmDEFFECTIVE_RETURN_STOCKS")
116 With FrmDEFFECTIVE_RETURN_STOCKS
117 If .lstdeffect.Items.Count = 0 Then Exit Sub
118 .lstdeffect.Focus()
119 If .rbDeffect.Checked Then
120 FrmDEFFECTIVE_STOCKS_ADD.txtpo2.Text = .lstdeffect.FocusedItem.SubItems(1).Text
121 FormShow(FrmDEFFECTIVE_STOCKS_ADD, True, .lstdeffect.FocusedItem.Text, 0)
122 ElseIf .rbReturn.Checked Then
123 FrmDEFFECTIVE_RETURN_ADD.txtpo2.Text = .lstdeffect.FocusedItem.SubItems(1).Text
124 FormShow(FrmDEFFECTIVE_RETURN_ADD, True, .lstdeffect.FocusedItem.Text, 0)
125 End If
126 End With
127 Case UCase("FrmORDER_FORM")
128 With FrmORDER_FORM
129 .lstOrder.Focus()
130 If .lstOrder.Items.Count > 0 Then
131 FormShow(FrmORDER_FORM_DATA, True, .lstOrder.FocusedItem.Text, 0)
132 End If
133 End With
134 Case UCase("frmphysicalcount")
135 With FrmPhysicalCount
136 If .lstphysical.Items.Count = 0 Then Exit Sub
137 .lstphysical.Focus()
138 .lstphysical.Select()
139 FormShow(FrmPHYSICALCOUNT_ADD, True, .lstphysical.FocusedItem.Text, 0)
140 End With
141 End Select
142 End Sub
143
144 'DELETE
145 Public Sub FormDelete(ByVal frmStr As String)
146 Select Case UCase(frmStr)
147 Case UCase("FrmCatList")
148 With FrmCatList
149 If .RBALL.Checked Or .RBCat.Checked Then
150 If MsgBox("Do you want to delete this record ???", MsgBoxStyle.YesNo + MsgBoxStyle.Information, "Sales and Inventory") = MsgBoxResult.Yes Then
151 'LIST ALL PRODUCT
152 If .RBALL.Checked Then
153 sqlSTR = "SELECT * FROM TBL_Stocks_Balances WHERE Item_ID =" & .lstCat.FocusedItem.Text
154 ExecuteSQLQuery(sqlSTR)
155 If sqlDT.Rows.Count > 0 Then
156 MsgBox("This item is currenty used in stock balances ", MsgBoxStyle.Information, "Sales and Inventory")
157 Exit Sub
158 Else
159 sqlSTR = "DELETE FROM TBL_Category_Item_File WHERE Item_ID =" & .lstCat.FocusedItem.Text
160 ExecuteSQLQuery(sqlSTR)
161 sqlSTR = "DELETE FROM TBL_Suppliers_Product WHERE Item_ID =" & .lstCat.FocusedItem.Text
162 ExecuteSQLQuery(sqlSTR)
163 'MsgBox("Record has been successfuly deleted !!!", MsgBoxStyle.Information, "Sales and Inventory")
164 ' FillListView(ExecuteSQLQuery("SELECT Item_ID as 'ID', Catg_ID as 'Category ID', Item_Name as 'Name', Item_Description as 'Description', Item_Barcode as 'Barcode', Item_Reorder_Point as 'Reorder Point', Item_Price as 'Price' FROM TBL_Category_Item_File"), .lstCat, 1)
165 FillListView(ExecuteSQLQuery("SELECT Item_ID as 'ID', Catg_ID as 'Category ID', Item_Name as 'Name', Item_Description as 'Description / Item Number', Item_Barcode as 'Barcode', Item_Reorder_Point as 'Reorder Point', Item_Org_Price as 'Price', Item_Price as 'Price (VAT 12%)', Unit_Measure as 'Measure' FROM TBL_Category_Item_File"), .lstCat, 1)
166
167 End If
168 Else
169 'BY CATEGORY
170 sqlSTR = "SELECT * FROM TBL_Category_Item_File WHERE Catg_ID =" & .lstCat.FocusedItem.Text
171 ExecuteSQLQuery(sqlSTR)
172 If sqlDT.Rows.Count > 0 Then
173 MsgBox("This category has detail(s) and in used by item file !!", MsgBoxStyle.Information, "Sales and Inventory")
174 Exit Sub
175 Else
176 sqlSTR = "DELETE FROM TBL_Category_File WHERE Catg_ID =" & .lstCat.FocusedItem.Text
177 ExecuteSQLQuery(sqlSTR)
178 ' MsgBox("Record has been successfuly deleted !!!", MsgBoxStyle.Information, "Sales and Inventory")
179 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'Category ID', Catg_Name as 'Category Name', Catg_Description as 'Category Description' FROM tbl_Category_File"), .lstCat, 1)
180 End If
181 End If
182 End If
183 End If
184 End With
185 Case UCase("FrmSuppliersList")
186 With FrmSuppliersList
187 If .lstsupplier.Items.Count > 0 Then
188 If MsgBox("Do you really want to delete this record ???", MsgBoxStyle.YesNo + MsgBoxStyle.Information, "Sales and Inventory") = MsgBoxResult.Yes Then
189 sqlSTR = "SELECT * FROM TBL_Purchase_Order WHERE Supp_ID =" & .lstsupplier.FocusedItem.Text
190 ExecuteSQLQuery(sqlSTR)
191 If sqlDT.Rows.Count > 0 Then
192 MsgBox("Record exists in purchase order form", MsgBoxStyle.Critical, "Sales and Inventory")
193 Exit Sub
194 Else
195 sqlSTR = "DELETE FROM TBL_Suppliers WHERE Supp_ID=" & .lstsupplier.FocusedItem.Text
196 ExecuteSQLQuery(sqlSTR)
197 sqlSTR = "DELETE FROM TBL_Suppliers_Product WHERE Supp_ID=" & .lstsupplier.FocusedItem.Text
198 ExecuteSQLQuery(sqlSTR)
199 'MsgBox("Record has been successfuly deleted", MsgBoxStyle.Information, "Sales and Inventory")
200 End If
201 End If
202 End If
203 End With
204 Case UCase("FrmPURCHASEORDER")
205 With FrmPURCHASEORDER
206 If .listorder.Items.Count > 0 Then
207 If .listorder.FocusedItem.SubItems(4).Text <> "Yes" Then
208 If MsgBox("Do you really want to delete this record ??", MsgBoxStyle.YesNo + MsgBoxStyle.Information, "Sales and Inventory") = MsgBoxResult.Yes Then
209 sqlSTR = "DELETE FROM TBL_Purchase_Order WHERE TBL_Purchase_Order.Purchase_ID =" & .listorder.FocusedItem.Text
210 ExecuteSQLQuery(sqlSTR)
211 sqlSTR = "DELETE FROM TBL_Purchase_Detail WHERE TBL_Purchase_Detail.Purchase_ID =" & .listorder.FocusedItem.Text
212 ExecuteSQLQuery(sqlSTR)
213 'MsgBox("Record has been successfuly deleted", MsgBoxStyle.Information, "Sales and Inventory")
214 sqlSTR = "SELECT Purchase_ID as 'ID', TBL_Suppliers.SuppName as 'Supplier Name', Delivery_Term as 'Delivery Term', Purchased_Date as 'Purchase Date', Approved" & _
215 " FROM TBL_Purchase_Order INNER JOIN TBL_Suppliers ON TBL_Purchase_Order.Supp_ID = TBL_Suppliers.Supp_ID ORDER BY Purchase_ID"
216 FillListView(ExecuteSQLQuery(sqlSTR), .listorder, 0)
217
218 End If
219 Else
220 MsgBox("This record has been APPROVED, delete not granted !!!", MsgBoxStyle.Information, "Sales and Inventory")
221 End If
222 End If
223 End With
224 Case UCase("frmSTOCKMONITORINGBALANCES")
225 With frmSTOCKMONITORINGBALANCES
226 sqlSTR = "SELECT * FROM TBL_Stocks_Balances WHERE Item_ID =" & .listStocks.FocusedItem.Text
227 ExecuteSQLQuery(sqlSTR)
228 If sqlDT.Rows(0)("Item_QTY") = 0 Then
229 sqlSTR = "DELETE FROM TBL_Stocks_Balances WHERE Item_ID =" & .listStocks.FocusedItem.Text
230 ExecuteSQLQuery(sqlSTR)
231 'MsgBox("Record successfuly deleted", MsgBoxStyle.Information, "Sales and Inventory")
232 sqlSTR = "SELECT TBL_category_item_file.item_id AS 'ID', TBL_Category_Item_File.Item_name as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Category_Item_File.item_price as 'Price', TBL_Stocks_Balances.Item_QTY as 'Quantity' , (tbl_stocks_balances.item_qty * tbl_category_item_file.item_price) as 'Total', DIRECT_INPUT AS 'DIRECT' " & _
233 "FROM TBL_category_item_file INNER JOIN TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID "
234 FillListView(ExecuteSQLQuery(sqlSTR), .listStocks, 0)
235 Else
236 MsgBox("Cannot delete this record, quantity is above zero !!", MsgBoxStyle.Information, "Sales and Inventory")
237 End If
238 End With
239 Case UCase("frmorder_form")
240 With FrmORDER_FORM
241 If .lstOrder.Items.Count > 0 Then
242 .lstOrder.Focus()
243 If MsgBox("Do you really want to delete this record ???", MsgBoxStyle.Information + MsgBoxStyle.YesNo, "Sales and Inventory") = MsgBoxResult.Yes Then
244 sqlSTR = "SELECT * FROM TBL_Sales_Receipt WHERE Order_No =" & .lstOrder.FocusedItem.Text
245 ExecuteSQLQuery(sqlSTR)
246 If sqlDT.Rows.Count > 0 Then
247 MsgBox("Can't continue deleting this record, Void this ORDER NO first !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
248 Exit Sub
249 Else
250 sqlSTR = "DELETE FROM TBL_Orders WHERE Order_No =" & .lstOrder.FocusedItem.Text
251 ExecuteSQLQuery(sqlSTR)
252 sqlSTR = "DELETE FROM TBL_Orders_Detail WHERE Order_No =" & .lstOrder.FocusedItem.Text
253 ExecuteSQLQuery(sqlSTR)
254 sqlSTR = "SELECT Order_No AS 'Order No', Order_Date AS 'Order Date', Product_Total AS 'TOTAL COST' FROM TBL_Orders WHERE Order_Date ='" & Format(.dtOrder.Value, "MM/dd/yyyy") & "'"
255 FillListView(ExecuteSQLQuery(sqlSTR), .lstOrder, 0)
256 End If
257 End If
258 End If
259 End With
260 End Select
261 End Sub
262 'SEARCH
263 Public Sub FormSearch(ByVal frmStr As String)
264 Select Case UCase(frmStr)
265 Case UCase("FrmCatList")
266 With FrmCatList
267 'If .RBALL.Checked Or .RBCat.Checked Or .rbcatitemlist.Checked Then
268 'MsgBox("here")
269 ' If Not .rbcatitemlist.Checked Then
270 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
271 .txtcatname.Text = ""
272 .grpCat.Visible = True
273 .RBCat.Enabled = False
274 .RBALL.Enabled = False
275 .rbcatitemlist.Enabled = False
276 .txtcatname.Select()
277 ' End If
278
279 'End If
280 End With
281 Case UCase("FrmSuppliersList")
282 With FrmSuppliersList
283 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
284 .txtname.Text = ""
285 .grpCat.Visible = True
286 .txtname.Select()
287 End With
288 Case UCase("FrmSUPPLIERSPRODUCT")
289 With FrmSUPPLIERSPRODUCT
290 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
291 .txtname.Text = ""
292 .grpCat.Visible = True
293 .txtname.Select()
294 End With
295 Case UCase("FrmPURCHASEORDER")
296 With FrmPURCHASEORDER
297 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
298 .txtname.Text = ""
299 .grpCat.Visible = True
300 .txtname.Select()
301 End With
302 Case UCase("FrmPURCHASEORDER_RECEIVE")
303 With FrmPURCHASEORDER_RECEIVE
304 '.Enabled = False
305 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
306 .txtname.Text = ""
307 .grpCat.Visible = True
308 .txtname.Select()
309 End With
310 Case UCase("frmSTOCKMONITORINGBALANCES")
311 With frmSTOCKMONITORINGBALANCES
312 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
313 .txtname.Text = ""
314 .grpCat.Visible = True
315 .txtname.Select()
316 End With
317 Case UCase("frmDeffective_Return_Stocks")
318 With FrmDEFFECTIVE_RETURN_STOCKS
319 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
320 .txtname.Text = ""
321 .grpCat.Visible = True
322 .txtname.Select()
323 End With
324 Case UCase("frmposreceipt_list")
325 With FrmPOSRECEIPT_LIST
326 .grpreceipt.Left = (.Width / 2) - (.grpreceipt.Width / 2)
327 .grpreceipt.Visible = True
328 .txtreceiptid.Text = ""
329 .txtreceiptid.Select()
330 End With
331 Case UCase("frmorder_form")
332 With FrmORDER_FORM
333 .txtorder.Text = 0
334 .grpCat.Left = (.Width / 2) - (.grpCat.Width / 2)
335 .grpCat.Visible = True
336 .txtorder.Select()
337 End With
338 End Select
339 End Sub
340
341 'PRINTING
342 Public Sub FormPrint(ByVal frmStr As String)
343 Dim Report As New FrmREPORTS
344 Select Case UCase(frmStr)
345 Case UCase("frmcatitemlist") 'CATEGORY ITEM LIST PRINT
346 globalFRM = "FrmCatITEMList"
347 FrmCATEGORY_ITEM_PRINT.ShowDialog()
348
349 Case UCase("FrmSuppliersList") 'PRINT SUPPLIER LIST
350 With FrmSuppliersList
351 If .rbsuplist.Checked Then
352 globalFRM = "FrmSuppliersList"
353 Rpt_SqlStr = "SELECT * FROM TBL_Suppliers ORDER BY SuppName"
354 Report.Show()
355 ElseIf .rbsuppro.Checked Then
356 globalFRM = "frmsuppliersproduct"
357 FrmSUPPLIER_PRODUCT_PRINT.ShowDialog()
358 End If
359 End With
360 'FrmREPORTS.Show()
361 Case UCase("frmsuppliersproduct") ' PRINT SUPPLIERS PRODUCT
362 globalFRM = "frmsuppliersproduct"
363 FrmSUPPLIER_PRODUCT_PRINT.ShowDialog()
364
365 Case UCase("FrmPURCHASEORDER") ' PRINT PURCHASE ORDER
366 With FrmPURCHASEORDER
367 If .rbpurchase.Checked Then
368 globalFRM = "FrmPURCHASEORDER"
369 FrmPURCHASE_ORDER_PRINT.ShowDialog()
370 Else
371 globalFRM = "frmpurchaseorder_receive"
372 FrmRECEIVE_ORDER_PRINT.ShowDialog()
373 End If
374 End With
375
376 Case UCase("frmpurchaseorder_receive")
377 globalFRM = "frmpurchaseorder_receive"
378 FrmRECEIVE_ORDER_PRINT.ShowDialog()
379
380 Case UCase("FrmSTOCKMONITORINGBALANCES")
381 globalFRM = "FrmSTOCKMONITORINGBALANCES"
382 Rpt_SqlStr = "SELECT *, * FROM TBL_Stocks_Balances " & _
383 "INNER JOIN TBL_Category_Item_File ON TBL_Stocks_Balances.Item_ID =TBL_Category_Item_File.Item_ID "
384 Report.Show()
385 'FrmREPORTS.Show()
386
387 Case UCase("FrmPRODUCTS_REORDER")
388 globalFRM = "FrmPRODUCTS_REORDER"
389 Rpt_SqlStr = "SELECT * FROM TBL_Category_Item_File " & _
390 "WHERE Item_ID IN (SELECT Item_ID FROM TBL_Stocks_Balances WHERE Item_QTY <= Item_Reorder_Point)"
391 Report.Show()
392 'FrmREPORTS.Show()
393
394 Case UCase("FrmDEFFECTIVE_RETURN_STOCKS")
395 With FrmDEFFECTIVE_RETURN_STOCKS
396 If .rbDeffect.Checked Then
397 globalFRM = "deffective_po_report"
398 FrmDEFFECTIVE_STOCKS_PRINT.ShowDialog()
399 ElseIf .rbReturn.Checked Then
400 'globalFRM = "deffective_po_return"
401 'FrmDEFFECTIVE_STOCKS_RETURN.ShowDialog()
402 FrmReturn_PRINT.ShowDialog()
403 End If
404 End With
405 Case UCase("frmcatlist")
406 globalFRM = "FrmCatITEMList"
407 With FrmCatList
408 If .rbcatitemlist.Checked Then
409 If .lstCategory.Items.Count = 0 Then Exit Sub
410 .lstCategory.Focus()
411 .lstCategory.Select()
412 Rpt_SqlStr = "SELECT * FROM TBL_Category_Item_File WHERE Catg_ID =" & .lstCategory.FocusedItem.Text & " ORDER BY Item_Name"
413 Report.Show()
414 'FrmREPORTS.Show()
415 ElseIf .RBALL.Checked Then
416 Rpt_SqlStr = "SELECT * FROM TBL_Category_Item_File ORDER BY Item_Name"
417 Report.Show()
418 'FrmREPORTS.Show()
419 End If
420 End With
421 Case UCase("frmorder_form")
422 With FrmORDER_FORM
423 If .lstOrder.Items.Count > 0 Then
424 .lstOrder.Focus()
425 globalFRM = "frmorder_form"
426 Rpt_SqlStr = "SELECT * FROM TBL_Orders WHERE Order_No =" & .lstOrder.FocusedItem.Text
427 Report.Show()
428 End If
429 End With
430 'Case UCase("frmorder_form_data")
431 Case UCase("frmposreceipt_list")
432 With FrmPOSRECEIPT_LIST
433 If .lstreceipt.Items.Count > 0 Then
434 .lstreceipt.Focus()
435 ' MsgBox(FrmPOSRECEIPT_LIST.lstreceipt.FocusedItem.SubItems(1).Text)
436 Rpt_SqlStr = "SELECT * FROM TBL_Sales_Sold_Detail WHERE Sales_ID =" & .lstreceipt.FocusedItem.SubItems(2).Text
437 'MsgBox(Rpt_SqlStr)
438 globalFRM = "frmPOSPAYMENT"
439 'FrmREPORTS.Show()
440 Report.Show()
441 End If
442 End With
443 Case UCase("frmaudit_trail")
444 With FrmAUDIT_TRAIL
445 'Rpt_SqlStr = "SELECT * FROM TBL_Audit_Log WHERE User_ID = " & Split(.cmbusers.Text, " - ")(0)
446 Rpt_SqlStr = "SELECT * " & _
447 "FROM TBL_Audit_Log " & _
448 "INNER JOIN TBL_Audit_Trail ON TBL_Audit_Log.User_ID = TBL_Audit_Trail.User_ID " & _
449 "AND TBL_Audit_Trail.Log_ID = TBL_Audit_Log.Log_ID " & _
450 "WHERE TBL_Audit_Trail.User_ID = " & Split(.cmbusers.Text, " - ")(0) & _
451 " AND Date >='" & Format(.dtfrom.Value, "MM/dd/yyyy") & "' AND Date <='" & Format(.dtto.Value, "MM/dd/yyyy") & "' ORDER BY Audit_ID"
452 ' Rpt_SqlStr = "Select * " & _
453 '"from( " & _
454 '"select row_number() over(order by User_ID) as row_number, * " & _
455 '"from SaleInv_DB.dbo.TBL_Audit_Trail ) t " & _
456 '"where row_number between 6 and 10 "
457 'MsgBox(Rpt_SqlStr)
458 globalFRM = "FRMAUDIT_TRAIL"
459 Report.Show()
460 End With
461 Case UCase("frmphysicalcount")
462 With FrmPhysicalCount
463 'Rpt_SqlStr = "SELECT * FROM TBL_Physical_Count " & _
464 ' "INNER JOIN TBL_Physical_Count_Details ON TBL_Physical_Count.P_ID = TBL_Physical_Count_Details.P_ID " & _
465 ' "INNER JOIN TBL_Category_Item_File ON TBL_Physical_Count_Details.Item_ID = TBL_Category_Item_File.Item_ID " & _
466 ' "WHERE P_DATE >='" & Format(.dtfrom.Value, "MM/dd/yyyy") & _
467 ' "' AND P_DATE <='" & Format(.dtto.Value, "MM/dd/yyyy") & "'"
468
469 'MsgBox(sqlDT.Rows.Count)
470 Rpt_SqlStr = "SELECT * FROM TBL_Physical_Count " & _
471 "INNER JOIN TBL_Physical_Count_Details ON TBL_Physical_Count.P_ID = TBL_Physical_Count_Details.P_ID " & _
472 "WHERE P_DATE >='" & Format(.dtfrom.Value, "MM/dd/yyyy") & _
473 "' AND P_DATE <='" & Format(.dtto.Value, "MM/dd/yyyy") & "'"
474 ExecuteSQLQuery(Rpt_SqlStr)
475 globalFRM = "frmphysicalcount"
476 Report.Show()
477 End With
478
479 End Select
480 End Sub
481
482 'REFRESH
483 Public Sub RefreshList(ByVal frmStr As String)
484 Dim and_SQL As String
485 Select Case UCase(frmStr)
486 Case UCase("FrmCatITEMList")
487 With FrmCatITEMList
488 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'Category ID', Catg_Name as 'Category Name', Catg_Description as 'Category Description' FROM tbl_Category_File"), .lstCategory, 0)
489 .lstCategory.Focus()
490 .lstCategory.Select()
491 End With
492 Case UCase("FrmCatList")
493 With FrmCatList
494 If .RBCat.Checked Then
495 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'Category ID', Catg_Name as 'Category Name', Catg_Description as 'Category Description' FROM tbl_Category_File"), .lstCat, 0)
496 .lstCat.Focus()
497 .lstCat.Select()
498 ElseIf .rbcatitemlist.Checked Then
499 FillListView(ExecuteSQLQuery("SELECT Catg_ID as 'Category ID', Catg_Name as 'Category Name', Catg_Description as 'Category Description' FROM tbl_Category_File"), .lstCategory, 0)
500 .lstCategory.Focus()
501 .lstCategory.Select()
502 ElseIf .RBALL.Checked Then
503 'MsgBox("here")
504 'FillListView(ExecuteSQLQuery("SELECT Item_ID as 'ID', Catg_ID as 'Category ID', replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', Item_Description as 'Description', Item_Barcode as 'Barcode', Item_Reorder_Point as 'Reorder Point', Item_Org_Price as 'Price', Item_Price as 'Price (VAT 12%)', Unit_Measure as 'Measure' FROM TBL_Category_Item_File ORDER BY Item_Name"), .lstCat, 0)
505 'FillListView(ExecuteSQLQuery("SELECT Item_ID as 'Item ID', Item_Name as 'Item Name', Item_Description as 'Description', Item_Barcode as 'Barcode', Item_Reorder_Point as 'Reorder Point', Unit_Measure as 'Measure', Item_Price as 'Price' FROM tbl_Category_Item_File WHERE Catg_ID =" & .lstCategory.FocusedItem.Text), .lstItems, 1)
506 FillListView(ExecuteSQLQuery("SELECT Item_ID as 'ID', TBL_Category_Item_File.Catg_ID as 'Category ID', Group_Name AS 'Brand' ,replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', Item_Description as 'Description / Item Number', Item_Barcode as 'Barcode', Item_Reorder_Point as 'Reorder Point', Item_Org_Price as 'Price', Item_Price as 'Price (VAT 12%)', Unit_Measure as 'Measure' " & _
507 "FROM TBL_Category_Item_File " & _
508 "INNER JOIN TBL_Category_File ON TBL_Category_Item_File.Catg_ID = TBL_Category_File.Catg_ID " & _
509 "INNER JOIN TBL_Group ON TBL_Category_File.Group_ID = TBL_Group.Group_ID " & _
510 "ORDER BY Item_Name"), .lstCat, 0)
511 ElseIf .RBGroup.Checked Then
512 FillListView(ExecuteSQLQuery("SELECT Group_ID AS 'ID', Group_Name AS 'Name', Group_Description AS 'Description' FROM TBL_Group"), .lstCat, 0)
513 End If
514 End With
515 Case UCase("FrmDEFFECTIVE_RETURN_STOCKS")
516 With FrmDEFFECTIVE_RETURN_STOCKS
517 Dim xGo As Boolean
518 If .rbpending.Checked Then
519 sqlSTR = "SELECT Pending_ID as 'Pending ID', replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', Item_Description as 'Description / Item Number',Pending_Date as 'Date', Item_QTY as 'Quantity', Receipt_ID AS 'Receipt No' " & _
520 "FROM TBL_Pending_Item " & _
521 "INNER JOIN TBL_Category_Item_File ON TBL_Pending_Item.Item_ID = TBL_Category_Item_File.Item_ID " & _
522 "WHERE Returnx = 'No' AND Pending_Date ='" & Format(.dtreturn.Value, "MM/dd/yyyy") & "'"
523 FillListView(ExecuteSQLQuery(sqlSTR), .lstdeffect, 0)
524
525
526 For x = 0 To .lstdeffect.Items.Count - 1
527 'MsgBox(lstdeffect.Items(x).Text)
528 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Details " & _
529 "INNER JOIN TBL_Deffective_PO_Return ON TBL_Deffective_PO_Details.DEF_PO_ID = TBL_Deffective_PO_Return.DEF_PO_ID " & _
530 "INNER JOIN TBL_Deffective_PO_Return_Details ON TBL_Deffective_PO_Return.Return_ID = TBL_Deffective_PO_Return_Details.Return_ID " & _
531 "INNER JOIN TBL_Category_Item_File ON TBL_Deffective_PO_Return_Details.Item_ID = TBL_Category_Item_File.Item_ID " & _
532 "WHERE TBL_Deffective_PO_Details.Pending_ID =" & .lstdeffect.Items(x).Text
533 ExecuteSQLQuery(sqlSTR)
534 If sqlDT.Rows.Count > 0 Then
535 For i = 0 To sqlDT.Rows.Count - 1
536 'MsgBox(sqlDT.Rows(i)("DEF_QTY") & " " & sqlDT.Rows(i)("Return_QTY"))
537 If CDbl(sqlDT.Rows(i)("Def_QTY")) = CDbl(sqlDT.Rows(i)("Return_QTY")) Then
538 .lstdeffect.Items(x).ForeColor = Color.Brown
539 Else
540 xGo = True
541 .lstdeffect.Items(x).ForeColor = Color.DarkBlue
542 End If
543 Next
544 Else
545 If Not xGo Then
546 sqlSTR = "SELECT * FROM TBL_Deffective_PO WHERE Pending_ID =" & .lstdeffect.Items(x).Text
547 ExecuteSQLQuery(sqlSTR)
548 If sqlDT.Rows.Count > 0 Then
549 .lstdeffect.Items(x).ForeColor = Color.YellowGreen
550 Else
551 .lstdeffect.Items(x).ForeColor = Color.Black
552 End If
553 End If
554 End If
555 Next
556 ElseIf .rbDeffect.Checked Then
557 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', SupplierName as 'Supplier Name', Delivery_Term as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Pending_ID AS 'Pending ID' FROM TBL_Deffective_PO " & _
558 "WHERE Return_Date ='" & Format(.dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY DEF_PO_ID ASC"
559 FillListView(ExecuteSQLQuery(sqlSTR), .lstdeffect, 0)
560 For i = 0 To .lstdeffect.Items.Count - 1
561 sqlSTR = "SELECT * FROM TBL_Deffective_PO_Return WHERE Def_PO_ID =" & .lstdeffect.Items(i).Text & _
562 " AND Fully_Return='Yes'" & _
563 " ORDER BY Def_PO_ID ASC"
564 ExecuteSQLQuery(sqlSTR)
565 If sqlDT.Rows.Count > 0 Then
566 .lstdeffect.Items(i).ForeColor = Color.Brown
567 Else
568 .lstdeffect.Items(i).ForeColor = Color.Black
569 End If
570 Next
571 ElseIf .rbReturn.Checked Then
572 sqlSTR = "SELECT DEF_PO_ID AS 'Defective ID', Purchase_ID as 'Purchase No', Replace(Replace(SupplierName,'$.$',''''),'$..$',',') as 'Supplier Name', Replace(Replace(Delivery_Term,'$.$',''''),'$..$',',') as 'Delivery Term', Replace(Replace(Address,'$.$',''''),'$..$',',') AS 'Address', Fully_Return AS 'Return' FROM TBL_Deffective_PO_Return " & _
573 "WHERE Return_Date ='" & Format(.dtreturn.Value, "MM/dd/yyyy") & "' ORDER BY DEF_PO_ID ASC"
574 FillListView(ExecuteSQLQuery(sqlSTR), .lstdeffect, 0)
575 For i = 0 To .lstdeffect.Items.Count - 1
576 'MsgBox(.lstdeffect.Items(i).SubItems(5).Text)
577 If .lstdeffect.Items(i).SubItems(5).Text = "Yes" Then
578 .lstdeffect.Items(i).ForeColor = Color.Brown
579 Else
580 .lstdeffect.Items(i).ForeColor = Color.Black
581 End If
582 Next
583
584 End If
585 End With
586 Case UCase("FrmPURCHASEORDER")
587 With FrmPURCHASEORDER
588 If .rbpurchase.Checked Then
589 sqlSTR = "SELECT Purchase_ID as 'Purchase ID', TBL_Suppliers.SuppName as 'Supplier Name', Delivery_Term as 'Delivery Term', Purchased_Date as 'Purchase Date', Approved" & _
590 " FROM TBL_Purchase_Order INNER JOIN TBL_Suppliers ON TBL_Purchase_Order.Supp_ID = TBL_Suppliers.Supp_ID " & _
591 " WHERE TBL_Purchase_Order.Purchased_Date ='" & Format(.dtpurchased.Value, "MM/dd/yyyy") & "'" & _
592 " ORDER BY Purchase_ID"
593 FillListView(ExecuteSQLQuery(sqlSTR), .listorder, 0)
594 For i = 0 To .listorder.Items.Count - 1
595 If .listorder.Items(i).SubItems(4).Text = "Yes" Then
596 .listorder.Items(i).ForeColor = Color.Brown
597 Else
598 .listorder.Items(i).ForeColor = Color.Black
599 End If
600 Next
601 ElseIf .rbreceive.Checked Then
602 sqlSTR = "SELECT TBL_Purchase_Order.Purchase_ID as 'Purchase ID', TBL_Suppliers.SuppName as 'Supplier Name' " & _
603 ", Replace(Replace(TBL_Purchase_Order.Address,'$.$',''''),'$..$',',') as 'Address', TBL_Purchase_Order.Delivery_Term as 'Delivery Term' " & _
604 "FROM TBL_Purchase_Order " & _
605 "INNER JOIN TBL_Suppliers ON TBL_Purchase_Order.Supp_ID = TBL_Suppliers.Supp_ID " & _
606 "WHERE TBL_Purchase_Order.Approved = 'Yes' " & _
607 "AND TBL_Purchase_Order.Received_Date ='" & Format(.dtpurchased.Value, "MM/dd/yyyy") & "'"
608 FillListView(ExecuteSQLQuery(sqlSTR), .listorder, 0)
609
610 End If
611 End With
612 Case UCase("FrmPRODUCTS_REORDER")
613 With FrmPRODUCTS_REORDER
614 sqlSTR = "SELECT TBL_Category_Item_File.Item_ID as 'ID', Replace(Replace(Item_Name,'$.$',''''),'$..$',',') as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Stocks_Balances.Item_Price as 'Price', Item_Reorder_Point as 'Reorder Point', Item_QTY as 'CURRENT STOCKS' " & _
615 "FROM TBL_Category_Item_File INNER JOIN " & _
616 "TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID " & _
617 "WHERE TBL_Stocks_Balances.Item_QTY <= Item_Reorder_Point"
618 FillListView(ExecuteSQLQuery(sqlSTR), .lstreorder, 0)
619 For i = 0 To .lstreorder.Items.Count - 1
620 If Int(.lstreorder.Items(i).SubItems(5).Text) <= 0 Then
621 .lstreorder.Items(i).ForeColor = Color.Brown
622 End If
623 Next
624 End With
625 Case UCase("frmSTOCKMONITORINGBALANCES")
626 With frmSTOCKMONITORINGBALANCES
627 sqlSTR = "SELECT TBL_category_item_file.item_id AS 'ID', Replace(Replace(TBL_Category_Item_File.Item_Name,'$.$',''''),'$..$',',') as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Category_Item_File.Item_Barcode AS 'Barcode', TBL_Category_Item_File.Item_Reorder_Point AS 'Reorder Point', TBL_Category_Item_File.Item_Org_Price as 'Price W/O VAT',TBL_Category_Item_File.item_price as 'Price W/ VAT', TBL_Stocks_Balances.Item_QTY as 'Quantity' , (tbl_stocks_balances.item_qty * tbl_category_item_file.item_price) as 'Total'" & _
628 "FROM TBL_category_item_file INNER JOIN TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID ORDER BY TBL_Category_Item_File.Item_name"
629
630 ' sqlSTR = "SELECT TBL_category_item_file.item_id AS 'ID', TBL_Category_Item_File.Item_name as 'Name', TBL_Category_Item_File.Item_Description as 'Description', TBL_Category_Item_File.Item_Org_Price as 'Price W/O VAT',TBL_Category_Item_File.item_price as 'Price W/ VAT', TBL_Stocks_Balances.Item_QTY as 'Quantity' , (tbl_stocks_balances.item_qty * tbl_category_item_file.item_price) as 'Total', DIRECT_INPUT AS 'DIRECT' " & _
631 ' "FROM TBL_category_item_file INNER JOIN TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID ORDER BY TBL_Category_Item_File.Item_Name"
632 FillListView(ExecuteSQLQuery(sqlSTR), .listStocks, 0)
633 For i = 0 To .listStocks.Items.Count - 1
634 If Int(.listStocks.Items(i).SubItems(7).Text) <= 0 Then
635 .listStocks.Items(i).ForeColor = Color.Brown
636 End If
637 Next
638 End With
639 Case UCase("FrmSuppliersList")
640 With FrmSuppliersList
641 'ExecuteSQLQuery("select Supp_ID as 'Supplier ID', replace(replace(suppName,'$.$','''),'$..$',',') as 'Supplier Name', replace(replace(suppadd,'$.$','''),'$..$',',') as 'Address', suppcontact as 'Contact No', replace(replace(ContactPerson,'$.$','''),'$..$',',') as 'Contact Person' FROM tbl_suppliers ORDER BY suppName"
642 If .rbsuplist.Checked Then
643 FillListView(ExecuteSQLQuery("select Supp_ID as 'Supplier ID', replace(replace(suppname,'$.$',''''),'$..$',',') as 'Supplier Name', replace(replace(suppadd,'$.$',''''),'$..$',',') as 'Address', replace(replace(suppcontact,'$.$',''''),'$..$',',') as 'Contact No', replace(replace(contactperson,'$.$',''''),'$..$',',') as 'Contact Person' FROM tbl_suppliers ORDER BY suppName"), .lstsupplier, 0)
644 ElseIf .rbsuppro.Checked Then
645 FillListView(ExecuteSQLQuery("select Supp_ID as 'Supplier ID', replace(replace(suppname,'$.$',''''),'$..$',',') as 'Supplier Name', replace(replace(suppadd,'$.$',''''),'$..$',',') as 'Address', replace(replace(suppcontact,'$.$',''''),'$..$',',') as 'Contact No', replace(replace(contactperson,'$.$',''''),'$..$',',') as 'Contact Person' FROM tbl_suppliers"), .lstSuppliers, 0)
646 End If
647
648 End With
649 Case UCase("FrmSUPPLIERSPRODUCT")
650
651 With FrmSUPPLIERSPRODUCT
652 FillListView(ExecuteSQLQuery("select Supp_ID as 'Supplier ID', replace(replace(suppname,'$.$',''''),'$..$',',') as 'Supplier Name', replace(replace(suppadd,'$.$',''''),'$..$',',') as 'Address', replace(replace(suppcontact,'$.$',''''),'$..$',',') as 'Contact No', replace(replace(contactperson,'$.$',''''),'$..$',',') as 'Contact Person' FROM tbl_suppliers"), .lstSuppliers, 0)
653 End With
654 Case UCase("frmOrder_form")
655 With FrmORDER_FORM
656 sqlSTR = "SELECT Order_No AS 'Order No', Order_Date AS 'Order Date', Product_Total AS 'TOTAL COST' FROM TBL_Orders WHERE Order_Date ='" & Format(.dtOrder.Value, "MM/dd/yyyy") & "'"
657 FillListView(ExecuteSQLQuery(sqlSTR), .lstOrder, 0)
658 End With
659 Case UCase("frmposreceipt_list")
660 With FrmPOSRECEIPT_LIST
661 If .chckcollector.Checked Then
662 and_SQL = " AND User_ID =" & Split(.cmbcollector.Text, " - ")(0)
663 Else
664 and_SQL = ""
665 End If
666 sqlSTR = "SELECT Order_No AS 'Order No', Receipt_ID as 'Receipt No', Sales_ID as 'ID', VATable as 'Vatable', Total_Sale as 'Total Sale', Amount_Due as 'Amount Due', Void " & _
667 "FROM TBL_Sales_Receipt WHERE Receipt_Date >= '" & Format(.DtFrom.Value, "MM/dd/yyyy") & "' AND Receipt_Date <= '" & Format(.DtTo.Value, "MM/dd/yyyy") & "'"
668 'MsgBox(sqlSTR)
669 sqlSTR = sqlSTR & and_SQL
670 FillListView(ExecuteSQLQuery(sqlSTR), .lstreceipt, 0)
671 For i = 0 To .lstreceipt.Items.Count - 1
672 If .lstreceipt.Items(i).SubItems(6).Text = "Yes" Then
673 .lstreceipt.Items(i).ForeColor = Color.Brown
674 End If
675 Next
676 End With
677 Case UCase("frmaudit_trail")
678 With FrmAUDIT_TRAIL
679 If Split(.cmbusers.Text, " - ")(0) = "" Then
680 Exit Select
681 End If
682 sqlSTR = "SELECT Action, Date, Timex , LOGIN, LOGOUT " & _
683 "FROM (TBL_Audit_Trail " & _
684 "INNER JOIN TBL_Audit_Log ON TBL_Audit_Trail.User_ID = TBL_Audit_Log.User_ID " & _
685 " AND TBL_Audit_Trail.Log_ID = TBL_Audit_Log.Log_ID) " & _
686 "WHERE TBL_Audit_Trail.User_ID =" & Split(.cmbusers.Text, " - ")(0) & _
687 " AND Date >='" & Format(.dtfrom.Value, "MM/dd/yyyy") & "' AND Date <='" & Format(.dtto.Value, "MM/dd/yyyy") & "' ORDER BY Audit_ID"
688 ExecuteSQLQuery(sqlSTR)
689 .lstaudit.Items.Clear()
690 If sqlDT.Rows.Count > 0 Then
691 For i = 0 To sqlDT.Rows.Count - 1
692 .lstaudit.Items.Add(sqlDT.Rows(i)("Action"), 0)
693 .lstaudit.Items(.lstaudit.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Date"))
694 .lstaudit.Items(.lstaudit.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("Timex"))
695 .lstaudit.Items(.lstaudit.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("LOGIN"))
696 .lstaudit.Items(.lstaudit.Items.Count - 1).SubItems.Add(sqlDT.Rows(i)("LOGOUT"))
697 Next
698 End If
699 End With
700 Case UCase("frmphysicalcount")
701
702 With FrmPhysicalCount
703 sqlSTR = "SELECT P_ID AS 'P_ID', P_Date as 'DATE', Lastname + ', ' + Firstname + ' ' + Middlename AS ' Username' " & _
704 "FROM TBL_Physical_Count " & _
705 "INNER JOIN TBL_Users ON TBL_Physical_Count.User_ID = TBL_Users.User_ID " & _
706 " WHERE P_Date >='" & Format(.dtfrom.Value, "MM/dd/yyyy") & "' AND P_Date <='" & Format(.dtto.Value, "MM/dd/yyyy") & "'"
707 FillListView(ExecuteSQLQuery(sqlSTR), .lstphysical, 0)
708 End With
709 Case UCase("FrmBG")
710 Call refreshAdvisory()
711 End Select
712 End Sub
713
714 Private Sub refreshAdvisory()
715 With MDIMain
716 ' .toolStripClose.Enabled = False
717 ' Call xclose()
718 'Year sales
719 sqlSTR = "SELECT SUM(TBL_Sales_Receipt.AMOUNT_DUE) AS 'yearly_sales' FROM TBL_Sales_Receipt " & _
720 "INNER JOIN TBL_Sales_Sold ON TBL_Sales_Receipt.Sales_ID = TBL_Sales_Sold.Sales_ID " & _
721 "WHERE TBL_Sales_Receipt.Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
722 " AND YEAR(Receipt_Date) =" & Year(Today) & _
723 " GROUP BY YEAR(TBL_Sales_Receipt.Receipt_Date)"
724 ' " ORDER BY TBL_Sales_Sold.Sales_ID"
725 ExecuteSQLQuery(sqlSTR)
726 'MsgBox(sqlDT.Rows.Count)
727 If sqlDT.Rows.Count > 0 Then
728 .lblyrsales.Text = "Total Sales In This Year =Php " & Format(sqlDT.Rows(0)("yearly_sales"), "###,###,###.00")
729 Else
730 .lblyrsales.Text = "Total Sales In This Year =Php 0.00"
731 End If
732
733 'MONTHLY SALES
734 sqlSTR = "SELECT SUM(AMOUNT_DUE) AS 'monthly_sales' FROM TBL_Sales_Receipt " & _
735 "WHERE Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
736 " AND MONTH(Receipt_Date) =" & Month(Today) & _
737 " GROUP BY MONTH(TBL_Sales_Receipt.Receipt_Date) "
738 ExecuteSQLQuery(sqlSTR)
739 If sqlDT.Rows.Count > 0 Then
740 .lblmonthsales.Text = "Total Sales In This Month =Php " & Format(sqlDT.Rows(0)("monthly_sales"), "###,###,###.00")
741 Else
742 .lblmonthsales.Text = "Total Sales In This Month =Php 0.00"
743 End If
744
745 'TOTAL PRODUCTS
746 sqlSTR = "SELECT Count(Item_ID) AS 'xCount' FROM TBL_Category_Item_File "
747 ExecuteSQLQuery(sqlSTR)
748 If sqlDT.Rows.Count > 0 Then
749 .lbltotalprod.Text = "Total Products =" & Format(sqlDT.Rows(0)("xCount"), "###,###,###.00")
750 Else
751 .lbltotalprod.Text = "Total Products =0.00"
752 End If
753
754 'current inventory
755 sqlSTR = "SELECT SUM(Item_QTY) AS 'sumQTY', Count(Item_ID) AS 'xCount' FROM TBL_Stocks_Balances"
756 ExecuteSQLQuery(sqlSTR)
757 If sqlDT.Rows.Count > 0 And Int(sqlDT.Rows(0)("xCount")) <> 0 Then
758 .lblinventory.Text = "Current Iventory =" & sqlDT.Rows(0)("sumQTY")
759 .lblstock_tot_product.Text = "Current Iventory =" & sqlDT.Rows(0)("sumQTY")
760 .lblsalesagent_totalInventory.Text = "Total Inventory =" & sqlDT.Rows(0)("sumQTY")
761 Else
762 .lblinventory.Text = "Current Iventory =0"
763 .lblstock_tot_product.Text = "Current Iventory =0"
764 .lblsalesagent_totalInventory.Text = "Total Inventory =0"
765 End If
766
767 'UN-DELIVERD
768 'sqlSTR = "SELECT COUNT(Order_No) AS 'total_Order' FROM TBL_Orders " & _
769 ' "WHERE Order_No NOT IN(SELECT Order_No FROM TBL_Truck_Load_Dtl)"
770 'ExecuteSQLQuery(sqlSTR)
771 ' MsgBox(sqlDT.Rows(0)("total_order"))
772 'If sqlDT.Rows.Count > 0 Then
773 ' .lblundelivered.Text = "Un-delivered Order =" & sqlDT.Rows(0)("total_Order")
774 ' Else
775 ' .lblundelivered.Text = "Un-delivered Order =0.00"
776 ' End If
777
778 'REMAINING ORDER
779 sqlSTR = "SELECT COUNT(Order_No) AS 'Total_order' FROM TBL_Orders " & _
780 "WHERE Order_No NOT IN (SELECT Order_No FROM TBL_Sales_Receipt) "
781 ExecuteSQLQuery(sqlSTR)
782 If sqlDT.Rows.Count > 0 Then
783 .lblremainorder.Text = "Remaining Order No =" & sqlDT.Rows(0)("Total_Order")
784 Else
785 .lblremainorder.Text = "Remaining Order No =0.00"
786 End If
787
788 'CHECK REORDER LEVEL
789 sqlSTR = "SELECT TBL_Category_Item_File.Item_ID as 'ID', Item_Name as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Stocks_Balances.Item_Price as 'Price', Item_Reorder_Point as 'Reorder Point', Item_QTY as 'CURRENT STOCKS' " & _
790 "FROM TBL_Category_Item_File INNER JOIN " & _
791 "TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID " & _
792 "WHERE TBL_Stocks_Balances.Item_QTY <= Item_Reorder_Point"
793 ExecuteSQLQuery(sqlSTR)
794 If sqlDT.Rows.Count > 0 Then
795 .lblreorder.Text = "Products on Critical level =" & sqlDT.Rows.Count
796 .lblstock_critical.Text = "Product(s) on Critical =" & sqlDT.Rows.Count
797 .lblcashier_critical.Text = "Critical Product =" & sqlDT.Rows.Count
798 .lblsalesagent_Criticalproduct.Text = "Critical Product =" & sqlDT.Rows.Count
799 Else
800 .lblreorder.Text = "Products on Critical level =0"
801 .lblstock_critical.Text = "Product(s) on Critical =0"
802 .lblcashier_critical.Text = "Critical Product =0"
803 .lblsalesagent_Criticalproduct.Text = "Critical Product =0"
804 End If
805
806 'CHECK PURCHASE DETAIL FOR THE YEAR
807 'MsgBox("here")
808 sqlSTR = "SELECT distinct SUM(Purchase_Total) AS 'totalx' " & _
809 "FROM TBL_Purchase_Order " & _
810 "WHERE Year(Received_Date) =" & Year(Today) & _
811 " AND Approved ='Yes' " & _
812 "GROUP BY YEAR(TBL_Purchase_Order.Received_Date) "
813 ExecuteSQLQuery(sqlSTR)
814 If sqlDT.Rows.Count > 0 Then
815 .lblPurchaseYr.Text = "Amount Purchase This Year =Php " & Format(sqlDT.Rows(0)("totalx"), "###,###,###.00")
816 .lblsalesagent_purchaseyear.Text = "Amount Purchase For The Year =Php " & Format(sqlDT.Rows(0)("totalx"), "###,###,###.00")
817 Else
818 .lblPurchaseYr.Text = "Amount Purchase This Year =Php 0.00"
819 .lblsalesagent_purchaseyear.Text = "Amount Purchase For The Year =Php 0.00"
820 End If
821
822 'CHECK PURCHASE DETAIL FOR THE MONTH
823 sqlSTR = "SELECT SUM(Purchase_Total) AS 'totalx' " & _
824 "FROM TBL_Purchase_Order " & _
825 "WHERE Month(Received_Date) =" & Month(Today) & _
826 " AND Approved ='Yes' " & _
827 "GROUP BY MONTH(TBL_Purchase_Order.Received_Date) "
828 ExecuteSQLQuery(sqlSTR)
829 If sqlDT.Rows.Count > 0 Then
830 .lblpurchasemonth.Text = "Amount Purchase This Month =Php " & Format(sqlDT.Rows(0)("totalx"), "###,###,###.00")
831 .lblsalesagent_purchaseMonth.Text = "Amount Purchase For The Month =Php " & Format(sqlDT.Rows(0)("totalx"), "###,###,###.00")
832 Else
833 .lblpurchasemonth.Text = "Amount Purchase This Month =Php 0.00"
834 .lblsalesagent_purchaseMonth.Text = "Amount Purchase For The Month =Php 0.00"
835 End If
836
837 'receive order year
838 sqlSTR = "SELECT SUM(Item_QTY) AS 'totalx_Item' " & _
839 "FROM TBL_Purchase_Order " & _
840 "INNER JOIN TBL_Purchase_Detail ON TBL_Purchase_Detail.Purchase_ID = TBL_Purchase_Order.Purchase_ID " & _
841 "WHERE Year(Received_Date) =" & Year(Today) & _
842 " AND Approved ='Yes' " & _
843 "GROUP BY YEAR(TBL_Purchase_Order.Received_Date) "
844 ExecuteSQLQuery(sqlSTR)
845 If sqlDT.Rows.Count > 0 Then
846 .lblstock_received_year.Text = "Received order for the year =" & Format(sqlDT.Rows(0)("totalx_Item"), "###,###,###")
847 Else
848 .lblstock_received_year.Text = "Received order for the year =0"
849 End If
850
851 'receive order month
852 sqlSTR = "SELECT SUM(Item_QTY) AS 'totalx_Item' " & _
853 "FROM TBL_Purchase_Order " & _
854 "INNER JOIN TBL_Purchase_Detail ON TBL_Purchase_Detail.Purchase_ID = TBL_Purchase_Order.Purchase_ID " & _
855 "WHERE MONTH(Received_Date) =" & Month(Today) & _
856 " AND Approved ='Yes' " & _
857 "GROUP BY MONTH(TBL_Purchase_Order.Received_Date) "
858 ExecuteSQLQuery(sqlSTR)
859
860 If sqlDT.Rows.Count > 0 Then
861 .lblstock_received_month.Text = "Received order for the month =" & Format(sqlDT.Rows(0)("totalx_Item"), "###,###,###")
862 .lblsalesagent_totalorder.Text = "Total order for the month =" & Format(sqlDT.Rows(0)("totalx_Item"), "###,###,###")
863 Else
864 .lblstock_received_month.Text = "Received order for the month =0"
865 .lblsalesagent_totalorder.Text = "Total order for the month =0"
866 End If
867
868 'undeliver
869 sqlSTR = "SELECT SUM(Item_QTY) AS 'totalx_Item' , Count(TBL_Purchase_Order.Purchase_ID) AS 'xCount' " & _
870 "FROM TBL_Purchase_Order " & _
871 "INNER JOIN TBL_Purchase_Detail ON TBL_Purchase_Detail.Purchase_ID = TBL_Purchase_Order.Purchase_ID " & _
872 "WHERE Approved ='No' "
873 ExecuteSQLQuery(sqlSTR)
874 If sqlDT.Rows.Count > 0 And sqlDT.Rows(0)("xCount") <> 0 Then
875 .lblstock_Undeliver.Text = "Undeliver order =" & Format(sqlDT.Rows(0)("totalx_Item"), "###,###,###")
876 Else
877 .lblstock_Undeliver.Text = "Undeliver order =0"
878 End If
879
880 'cashier sales
881 sqlSTR = "SELECT SUM(TBL_Sales_Receipt.AMOUNT_DUE) AS 'daily' FROM TBL_Sales_Receipt " & _
882 "INNER JOIN TBL_Sales_Sold ON TBL_Sales_Receipt.Sales_ID = TBL_Sales_Sold.Sales_ID " & _
883 "WHERE TBL_Sales_Receipt.Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
884 " AND Receipt_Date ='" & Format(Today, "MM/dd/yyyy") & "'" & _
885 " AND TBL_Sales_Receipt.User_ID =" & xUser_ID & _
886 " GROUP BY YEAR(TBL_Sales_Receipt.Receipt_Date)"
887 ' " ORDER BY TBL_Sales_Sold.Sales_ID"
888 ExecuteSQLQuery(sqlSTR)
889 If sqlDT.Rows.Count > 0 Then
890 .lblcashier_DaySales.Text = "Cashier Sales =Php " & Format(sqlDT.Rows(0)("daily"), "###,###.00")
891 Else
892 .lblcashier_DaySales.Text = "Cashier Sales =Php 0.00"
893 End If
894
895 'Cashier Void
896 sqlSTR = "SELECT *, *, * FROM TBL_Sales_Receipt " & _
897 "INNER JOIN TBL_Sales_Sold ON TBL_Sales_Receipt.Sales_ID = TBL_Sales_Sold.Sales_ID " & _
898 "INNER JOIN TBL_Sales_Void ON TBL_Sales_Sold.Sales_ID = TBL_Sales_Void.Sales_ID " & _
899 " AND Receipt_Date ='" & Format(Today, "MM/dd/yyyy") & "'" & _
900 " AND TBL_Sales_Receipt.User_ID =" & xUser_ID
901 ' " GROUP BY YEAR(TBL_Sales_Receipt.Receipt_Date)"
902 ExecuteSQLQuery(sqlSTR)
903 If sqlDT.Rows.Count > 0 Then
904 .lblcashier_Void.Text = "Cashier Void =" & sqlDT.Rows.Count
905 Else
906 .lblcashier_Void.Text = "Cashier Void =0"
907 End If
908
909 'cashier Receipt
910 sqlSTR = "SELECT *, * FROM TBL_Sales_Receipt " & _
911 "INNER JOIN TBL_Sales_Sold ON TBL_Sales_Receipt.Sales_ID = TBL_Sales_Sold.Sales_ID " & _
912 "WHERE TBL_Sales_Receipt.Sales_ID NOT IN (SELECT Sales_ID FROM TBL_Sales_Void) " & _
913 " AND Receipt_Date ='" & Format(Today, "MM/dd/yyyy") & "'" & _
914 " AND TBL_Sales_Receipt.User_ID =" & xUser_ID
915 '" GROUP BY YEAR(TBL_Sales_Receipt.Receipt_Date)"
916 ExecuteSQLQuery(sqlSTR)
917 If sqlDT.Rows.Count > 0 Then
918 .lblcashier_totalReceipt.Text = "Total Receipt =" & sqlDT.Rows.Count
919 Else
920 .lblcashier_totalReceipt.Text = "Total Receipt =0"
921 End If
922
923 'With MDIMain
924 If UCase(xUser_Access) = UCase("administrator") Or UCase(xUser_Access) = UCase("Sales Agent") Or UCase(xUser_Access) = UCase("Stock Room") Then
925 sqlSTR = "SELECT TBL_Category_Item_File.Item_ID as 'ID', Item_Name as 'Name', TBL_Category_Item_File.Item_Description as 'Description / Item Number', TBL_Stocks_Balances.Item_Price as 'Price', Item_Reorder_Point as 'Reorder Point', Item_QTY as 'CURRENT STOCKS' " & _
926 "FROM TBL_Category_Item_File INNER JOIN " & _
927 "TBL_Stocks_Balances ON TBL_Category_Item_File.Item_ID = TBL_Stocks_Balances.Item_ID " & _
928 "WHERE TBL_Stocks_Balances.Item_QTY <= Item_Reorder_Point"
929 ExecuteSQLQuery(sqlSTR)
930
931 ' If sqlDT.Rows.Count > 0 Then
932 ' MsgBox("A Product(s) reach its critical level !!", MsgBoxStyle.Exclamation, "Sales and Inventory")
933 ' .tmrcritical.Enabled = True
934 'Else
935 ' .tmrcritical.Enabled = False
936 'End If
937
938 If sqlDT.Rows.Count > 0 Then
939 .tmrcritical.Enabled = True
940 Else
941 .tmrcritical.Enabled = False
942 .cmdProductReorder.Enabled = True
943 .cmdProductReorder.ForeColor = Color.Black
944 End If
945
946 Else
947 .tmrcritical.Enabled = False
948 .cmdProductReorder.ForeColor = Color.Black
949 End If
950 'End With
951 End With
952 End Sub
953
954 End Module
